package com.opentutor.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.opentutor.database.Database;
import com.opentutor.model.Topic;

public class TopicDaoImpl implements TopicDao {

	@Override
	public List<Topic> getAllTopics() {

		String query = "select * from topic";
		List<Topic> result = fetchTopicsFromDatabase(query);

		return result;
	}

	@Override
	public List<Topic> getAllTopicsOfACourse(int courseId) {
		
		String query = "select topic.id, topic.title, topic.description, topic.chapter_id from topic "
					+ " inner join chapter on topic.chapter_id = chapter.id "
					+ " where chapter.course_id = "
					+ 	courseId;

		List<Topic> result = fetchTopicsFromDatabase(query);

		return result;
	}

	@Override
	public List<Topic> getAllTopicsOfAChapter(int chapterId) {
		
		String query =   " select * from topic "
					   + " where chapter_id = " + chapterId;

		List<Topic> result = fetchTopicsFromDatabase(query);
		return result;
	}

	@Override
	public Topic getTopicById(int topicId) {
		String query = "select * from topic";
		List<Topic> result = fetchTopicsFromDatabase(query);

		if (result.size() == 1)
			return result.get(0);

		return null;
	}

	@Override
	public void addTopic(Topic topic) {
		try {

			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("insert into topic(title, description, chapter_id) values(?,?,?)");

			stmt.setString(1, topic.getTitle());
			stmt.setString(2, topic.getDescription());
			stmt.setInt(3, topic.getChapterId());

			stmt.executeUpdate();

		} catch (Exception e) {

			System.out.println(e.getMessage());
			e.printStackTrace();
		}

	}

	@Override
	public void updateTopic(Topic topic) {
		try {

			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("update topic set title = ?, description = ?, chapter_id = ?, updated_date = now() where id = ?");

			stmt.setString(1, topic.getTitle());
			stmt.setString(2, topic.getDescription());
			stmt.setInt(3, topic.getChapterId());
			stmt.setInt(4, topic.getId());

			stmt.executeUpdate();

		} catch (Exception e) {

			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}

	@Override
	public void deleteTopic(int topicId) {
		try {

			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn
					.prepareStatement("delete from topic where id = ?");

			stmt.setInt(1, topicId);

			stmt.executeUpdate();

		} catch (Exception e) {

			System.out.println(e.getMessage());
			e.printStackTrace();
		}
	}

	private List<Topic> fetchTopicsFromDatabase(String query) {
		try {
			Connection conn = Database.getConnection();
			PreparedStatement stmt = conn.prepareStatement(query);
			ResultSet result = stmt.executeQuery();

			List<Topic> results = getAllTopicsFromResultSet(result);

			return results;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	private List<Topic> getAllTopicsFromResultSet(ResultSet result)
			throws SQLException {
		List<Topic> chapters = new ArrayList<Topic>();
		while (result.next()) {
			Topic chapter = getTopicFromResultSet(result);
			chapters.add(chapter);
		}
		return chapters;
	}

	private Topic getTopicFromResultSet(ResultSet result) throws SQLException {
		Topic topic = new Topic();

		topic.setId(result.getInt("id"));
		topic.setTitle(result.getString("title"));
		topic.setDescription(result.getString("description"));
		topic.setChapterId(result.getInt("chapter_id"));

		return topic;
	}

}
